package com.zhgxi.zf.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import org.slave4j.orm.Compositor;
import org.slave4j.orm.Compositor.CompositorType;
import org.slave4j.orm.Filtration;
import org.slave4j.orm.Filtration.MatchType;
import org.slave4j.orm.hibernate.BaseDao;
import org.slave4j.orm.hibernate.BaseService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.zhgxi.zf.entity.Event;
import com.zhgxi.zf.entity.Report;
import com.zhgxi.zf.entity.User;
import com.zhgxi.zf.entity.WholeReport;

@Service
@Transactional
public class EventService extends BaseService<Event> {
	@Override
	@Resource(name = "eventDao")
	public void setBaseDao(BaseDao<Event> baseDao) {
		this.baseDao = baseDao;
	}

	public List<Event> findAllEvent(boolean isemergency, User user) {
		Filtration visible = new Filtration(MatchType.EQ, true, "visible");
		Filtration fisemergency = new Filtration(MatchType.EQ, isemergency,
				"isemergency");
		if (user != null && user.getRole().getId() > 1) {
			Filtration fuser = new Filtration(MatchType.EQ, user, "user");
			return baseDao.findList(visible, fisemergency, fuser);
		}
		
		Compositor com = new Compositor("etime",CompositorType.ASC);
		
		return baseDao.findList(com,visible, fisemergency);
	}

	public List<Event> findAllEvent() {
		Filtration visible = new Filtration(MatchType.EQ, true, "visible");
		Compositor com = new Compositor("etime",CompositorType.ASC);
		return baseDao.findList(com,visible);
	}

	public List<Event> searchEventByName(String ename, boolean isemergency,
			User user) {
		Filtration title = new Filtration(MatchType.LIKE, ename, "title");
		Compositor com = new Compositor("etime",CompositorType.ASC);
		Filtration fisemergency = new Filtration(MatchType.EQ, isemergency,
				"isemergency");
		Filtration visible = new Filtration(MatchType.EQ, true, "visible");
		if (user != null && user.getRole().getId() > 1) {
			Filtration fuser = new Filtration(MatchType.EQ, user, "user");
			return baseDao.findList(com,title, visible, fisemergency, fuser);
		}
		return baseDao.findList(com,title, fisemergency, visible);
	}

	public boolean delEvent(Event event) {
		try {
			baseDao.unVisible(event.getId());
			return true;
		} catch (Exception e) {
			return false;
		}
	}

	public boolean saveEvent(Event event) {
		try {
			if (event.isNew() || event.getId() == 0) {
				baseDao.save(event);
			} else {
				baseDao.update(event);
			}
			return true;
		} catch (RuntimeException e) {
			return false;
		}
	}

	/**
	 * @author elemeNT
	 * @param condition
	 *            条件集合
	 * @return
	 */
	public List<Event> searchEvent(HashMap<String, String> condition) {
		List<Filtration> conditionList = new ArrayList<Filtration>();
		Filtration visible = new Filtration(MatchType.EQ, true, "visible");
		conditionList.add(visible);
		conditionList.add(new Filtration(MatchType.EQ, false, "isemergency"));
		Iterator<String> keys = condition.keySet().iterator();
		while (keys.hasNext()) {
			String key = keys.next();
			if ("title".equals(key) || "place".equals(key)) {
				conditionList.add(new Filtration(MatchType.LIKE, condition
						.get(key), key));
			} else {
				try {
					conditionList.add(new Filtration(MatchType.EQ, Integer
							.parseInt(condition.get(key).trim()), key));
				} catch (NumberFormatException e) {
					e.printStackTrace();
				}
			}
		}
		List<Event> list = baseDao.findList(conditionList);
		return list;
	}

	@SuppressWarnings("deprecation")
	public List<Report> report() {

		String sql = "SELECT a.NAME AS dname,c.NAME AS tname,COUNT(b.id) AS COUNT FROM  zf_dept a,zf_event b,zf_event_type c WHERE c.id = b.type_id AND a.id = b.did AND a.visible = 1 AND b.visible = 1 GROUP BY a.id,b.type_id";
		List<Report> rlist = new ArrayList<Report>();

		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				Report report = new Report();
				report.setDname(rSet.getString("dname"));
				report.setTname(rSet.getString("tname"));
				report.setCount(rSet.getInt("count"));
				rlist.add(report);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return rlist;
	}

	@SuppressWarnings("deprecation")
	public List<Report> reportByDMY(String dmy) {
		dmy = dmy.replace(" ", ""); // 防止注入
		String sql = "SELECT a.NAME AS dname,c.NAME AS tname,COUNT(b.id) AS COUNT FROM  zf_dept a,zf_event b,zf_event_type c WHERE c.id = b.type_id AND a.id = b.did AND b.etime like ? AND a.visible = 1 AND b.visible = 1 GROUP BY a.id,b.type_id";
		List<Report> rlist = new ArrayList<Report>();

		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dmy + "%");
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				Report report = new Report();
				report.setDname(rSet.getString("dname"));
				report.setTname(rSet.getString("tname"));
				report.setCount(rSet.getInt("count"));
				rlist.add(report);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return rlist;
	}

	@SuppressWarnings("deprecation")
	public List<WholeReport> reportByYArea(String dmy) {
		dmy = dmy.replace(" ", ""); // 防止注入
		String sql = "SELECT a.NAME AS NAME,COUNT(b.id) AS COUNT FROM zf_dept AS a LEFT JOIN (SELECT * FROM zf_event WHERE visible=1 and etime like ?) AS b ON a.id = b.did WHERE  a.visible = 1 GROUP BY a.id";
		List<WholeReport> rlist = new ArrayList<WholeReport>();

		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, dmy + "%");
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				WholeReport wreport = new WholeReport();
				wreport.setName(rSet.getString("name"));
				wreport.setCount(rSet.getInt("count"));
				rlist.add(wreport);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return rlist;
	}

	@SuppressWarnings("deprecation")
	public List<WholeReport> countEventByY(String y) {
		y = y.replace(" ", ""); // 防止注入
		String sql = "SELECT c.NAME AS name,COUNT(b.id) AS COUNT FROM zf_event b,zf_event_type c WHERE c.id = b.type_id AND b.etime like ? AND b.visible = 1 GROUP BY b.type_id";
		List<WholeReport> rlist = new ArrayList<WholeReport>();

		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, y + "%");
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				WholeReport wreport = new WholeReport();
				wreport.setName(rSet.getString("name"));
				wreport.setCount(rSet.getInt("count"));
				rlist.add(wreport);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return rlist;
	}

	@SuppressWarnings("deprecation")
	public List<Report> reportByDD(String start, String end) {
		start = start.replace(" ", ""); // 防止注入
		end = end.replace(" ", ""); // 防止注入
		String sql = "SELECT a.NAME AS dname,c.NAME AS tname,COUNT(b.id) AS COUNT FROM  zf_dept a,zf_event b,zf_event_type c WHERE c.id = b.type_id AND a.id = b.did AND b.etime BETWEEN ? and ? AND a.visible = 1 AND b.visible = 1 GROUP BY a.id,b.type_id";
		List<Report> rlist = new ArrayList<Report>();

		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, start);
			pstmt.setString(2, end);
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				Report report = new Report();
				report.setDname(rSet.getString("dname"));
				report.setTname(rSet.getString("tname"));
				report.setCount(rSet.getInt("count"));
				rlist.add(report);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return rlist;
	}

	@SuppressWarnings("deprecation")
	public int countPass(int iserge) {
		String sql = "SELECT count(*) as count FROM zf_event WHERE visible=1 AND ispass	= 0 AND isemergency = ?";
		int r = 0;
		try {
			Connection conn = baseDao.getSession().connection();
			PreparedStatement pstmt = null;
			ResultSet rSet = null;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, iserge);
			rSet = pstmt.executeQuery();
			while (rSet.next()) {
				r = rSet.getInt("count");
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
		}
		return r;
	}

}
